'use strict';
const StrUtils = require('../utils/myutils/pack/StrUtils');
const { Service } = require('ee-core');
const Storage = require('ee-core/storage');
const _ = require('lodash');
const path = require('path');
const Log = require('ee-core/log');

class StorageService extends Service {

    constructor (ctx) {
        super(ctx);
        // sqlite数据库
        this.sqliteFile = 'sqlite-demo.db';
        let sqliteOptions = {
            driver: 'sqlite',
            default: {
                timeout: 6000,
                verbose: console.log // 打印sql语法
            }
        }
        this.sqliteDB = Storage.connection(this.sqliteFile, sqliteOptions);
    }

    /*
     * get data dir (sqlite)
     */
    async getDataDir() {
        return this.sqliteDB.getStorageDir();
    }

    /*
      * 检查并创建表 (sqlite)
      */
    async checkAndCreateTableSqlite(tableName = '',fieldArr = []) {
        if (_.isEmpty(tableName)) {
            throw new Error(`table name is required`);
        }
        // 检查表是否存在
        const userTable = this.sqliteDB.db.prepare('SELECT * FROM sqlite_master WHERE type=? AND name = ?');
        const result = userTable.get('table', tableName);
        if (result) {
            return;
        }
        // 创建表
        const createTable = `
        CREATE TABLE ${tableName}
        (`+this.assField(fieldArr)+`);`
        this.sqliteDB.db.exec(createTable);

    }
    async addTableItem(tableName = '',itemName,itemType){
        this.sqliteDB.db.exec( `ALTER TABLE ${tableName} ADD COLUMN ${itemName} ${itemType}`);
    }

    async add(tableName,data){
        let sql = `INSERT INTO ${tableName} {} VALUES {}`
        let kv = this.getTableKeys(this.getKeys(data))
        sql = StrUtils.formant(sql,[kv.key,kv.val])
        const insert = this.sqliteDB.db.prepare(sql);
        insert.run(data)
        return true;
    }

    async select(sql){
        const selectUser = this.sqliteDB.db.prepare(sql);
        return selectUser.all();
    }
    async insert(sql){
        return this.run(sql)
    }
    async update(sql){
        return this.run(sql)
    }
    async delete(sql){
        return this.run(sql)
    }
    run(sql){
        const insert =  this.sqliteDB.db.prepare(sql);
        return insert.run();
    }

    getKeys(item = {}){
        let keys = []
        for (let key in item){
            keys.push(key)
        }
        return keys
    }
    getTableKeys(keys = []){
        let tk = ""
        let tv = ""
        for (let key of keys){
            tk+=`${key} `
            tv+=`@${key} `
            if (keys.length-1!==keys.indexOf(key)){
                tk+=","
                tv+=","
            }
        }
        return {
            key:`(${tk})`,
            val:`(${tv})`
        }
    }
    /*   let arr = [
             {itemName:"字段名称",type:"类型",isKey:"是否主键",isAdd:"是否自增",isNotNull:"是否不为空"}
         ]*/
    //新建表 组装字段，json转字符串
    assField(arr = []) {
        let fieldStr = ""
        for (let index = 0;index<arr.length;index++){
            let item = arr[index]
            fieldStr+=`${item.itemName} ${item.type}`
            if (item.isKey){
                fieldStr+=` PRIMARY KEY`
            }
            if (item.isAdd){
                fieldStr+=` AUTOINCREMENT`
            }
            if (item.isNotNull){
                fieldStr+=` NOT NULL`
            }
            if(index !== arr.length-1){
                fieldStr+=", "
            }
            fieldStr+="\n"
        }
        return fieldStr
    }
    /*
     * 增 Test data (sqlite)
     */
/*    async addTestDataSqlite(data) {
        //console.log("add data:", data);

        let table = 'user';
        await this.checkAndCreateTableSqlite(table);
        const insert = this.sqliteDB.db.prepare(`INSERT INTO ${table} (name, age) VALUES (@name, @age)`);
        insert.run(data);

        return true;
    }*/

    /*
     * 删 Test data (sqlite)
     */
/*    async delTestDataSqlite(name = '') {
        //console.log("delete name:", name);

        let table = 'user';
        await this.checkAndCreateTableSqlite(table);

        const delUser = this.sqliteDB.db.prepare(`DELETE FROM ${table} WHERE name = ?`);
        delUser.run(name);

        return true;
    }*/

    /*
     * 改 Test data (sqlite)
     */
/*    async updateTestDataSqlite(name= '', age = 0) {
        //console.log("update :", {name, age});

        let table = 'user';
        await this.checkAndCreateTableSqlite(table);

        const updateUser = this.sqliteDB.db.prepare(`UPDATE ${table} SET age = ? WHERE name = ?`);
        updateUser.run(age, name);

        return true;
    }*/

    /*
     * 查 Test data (sqlite)
     */
/*    async getTestDataSqlite(age = 0) {
        //console.log("select :", {age});

        let table = 'user';
        await this.checkAndCreateTableSqlite(table);

        const selectUser = this.sqliteDB.db.prepare(`SELECT * FROM ${table} WHERE age = @age`);
        const users = selectUser.all({age: age});
        //console.log("select users:", users);
        return users;
    }*/

    /*
     * all Test data (sqlite)
     */
/*    async getAllTestDataSqlite() {
        //console.log("select all user");

        let table = 'user';
        await this.checkAndCreateTableSqlite(table);

        const selectAllUser = this.sqliteDB.db.prepare(`SELECT * FROM ${table} `);
        //console.log("select allUser:", allUser);
        return selectAllUser.all();
    }*/

    /*
     * set custom data dir (sqlite)
     */
/*    async setCustomDataDir(dir) {
        if (_.isEmpty(dir)) {
            return;
        }

        // the absolute path of the db file
        const dbFile = path.join(dir, this.sqliteFile);
        const sqliteOptions = {
            driver: 'sqlite',
            default: {
                timeout: 6000,
                verbose: console.log
            }
        }
        this.sqliteDB = Storage.connection(dbFile, sqliteOptions);
    }*/
}
StorageService.toString = () => '[class StorageService]';
module.exports = StorageService;
